package com.fsnbigdata.repository;
import com.fsnbigdata.domain.Specimen;
import java.util.List;

import com.fsnbigdata.domain.dto.NamingPair4;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = SpecimenRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Specimen.class)
@Transactional(readOnly = true)
public interface SpecimenRepository extends DetachableJpaRepository<Specimen, Long>, SpecimenRepositoryCustom {

    List<NamingPair4> findAllByInitial(String initial);

    long countByCreator(String creator);

    List <Specimen> findAllByType(Integer type);

    List<NamingPair4> findAll(Specification specification);

    List<NamingPair4> findByInitialOrderByIdDesc(String initial);

    List<NamingPair4> findByInitialOrderByIdAsc(String initial);

    List<NamingPair4> findByInitialOrderByCollectDateDesc(String initial);

    List<NamingPair4> findByInitialOrderByCollectDateAsc(String initial);

    Page<Specimen> findAll(Specification<Specimen> spec, Pageable pageable);

    @Query(value = "select count(*) from specimen where \n" +
            "if(?1 !='',name like concat('%',?1,'%'),1=1) \n" +
            "AND if(?2 !='',status like concat('%',?2,'%'),1=1) \n" +
            "AND if(?3 !='',collect_number like concat('%',?3,'%'),1=1) \n" +
            "AND if(?4 !='',collect_person like concat('%',?4,'%'),1=1) \n" +
            "AND if(?5 !='',type =?5,1=1) \n" +
            "AND if(?6 !='',collect_date like concat('%',?6,'%'),1=1) \n" +
            "AND if(?7 !='',collect_location like concat('%',?7,'%'),1=1)",nativeQuery = true)
    Long queryCountByKey(String name,String status,String collectNumber,String collectPerson,
                         String type,String collectDate,String collectLocation);

    @Query(value = "select id,name,latin_name,collect_number,collect_location,collect_person,collect_date from specimen where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',status like concat('%',?2,'%'),1=1) AND if(?3 !='',collect_number like concat('%',?3,'%'),1=1) AND if(?4 !='',collect_person like concat('%',?4,'%'),1=1) AND if(?5 !='',type =?5,1=1) AND if(?6 !='',collect_date like concat('%',?6,'%'),1=1) AND if(?7 !='',collect_location like concat('%',?7,'%'),1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from specimen where \n" +
                    "if(?1 !='',name like concat('%',?1,'%'),1=1) \n" +
                    "AND if(?2 !='',status like concat('%',?2,'%'),1=1) \n" +
                    "AND if(?3 !='',collect_number like concat('%',?3,'%'),1=1) \n" +
                    "AND if(?4 !='',collect_person like concat('%',?4,'%'),1=1) \n" +
                    "AND if(?5 !='',type =?5,1=1) \n" +
                    "AND if(?6 !='',collect_date like concat('%',?6,'%'),1=1) \n" +
                    "AND if(?7 !='',collect_location like concat('%',?7,'%'),1=1)",nativeQuery = true)
    List<Object> queryAllByKey(String name,String status,String collectNumber,String collectPerson,
                               String type,String collectDate,String collectLocation, Pageable pageable);

    @Query(value = "select * from specimen",nativeQuery = true)
    List<Object> getCount();
}
